﻿using System;
using System.Data;
using System.Data.Common;
using System.Collections.Generic;
using Microsoft.JCI.Common.Utility;
using System.Text;


namespace Microsoft.JCI.Dal
{
    public partial class DisDispatchFormDal : DalBase
    {
        public DataTable QueryDispatchForm(List<SqlDbParameter> Conditions)
        {
            StringBuilder sqlStr = new StringBuilder();

            sqlStr.Append(@"
select * from 
(
select 
a.*,
b.Name,
b.Address,
b.AreaCode,
b.City,
b.Country,
b.Email,
b.Gender,
b.HomePhone,
b.PostCode,
b.Province,
b.Remark,
b.Type,
b.Mobile,
c.ValueCN as ProvinceName,
d.ValueCN as CityName,
(case when ReservationDate is null then 1 else
case when ReservationDate is not null and IsOnsite is null or IsOnsite = 0 then 2 else 3 end
end) FormSubStatus,
(case when e.Name is null then '未到达' else e.Name end) as CurrentBranchName,
(case when f.Name is null then '未到达' else f.Name end) as CurrentVendorName
from Jci_Dis_DispatchForm a
left join Jci_Mad_Customer b on a.CustomerId = b.CustomerId 
left join Jci_Sys_Dictionary c on b.Province = c.Code and c.DictionaryType = 2
left join Jci_Sys_Dictionary d on b.City = d.Code and d.DictionaryType=3
left join dbo.Jci_Sys_Company e on e.CompanyCode = a.AllocatedBranch
left join dbo.Jci_Sys_Company f on f.CompanyCode = a.AllocatedVendor
) tmp
");

            string sqlFormat = sqlStr.Append(" where 1=1 {1}").ToString();
            string sql = SqlDbParameter.BuildSqlString(sqlFormat, Conditions);
            DbCommand dbCommand = this._db.GetSqlStringCommand(sql);
            foreach (SqlDbParameter parameter in Conditions)
            {
                parameter.SetDbCommond(dbCommand);
            }
            DataSet ds = this._db.ExecuteDataSet(dbCommand);
            return ds.Tables[0];
        }
    }
}
